﻿using Dapper;
using Himp.TaskScheduling.Hangfire.Models;
using Microsoft.Data.SqlClient;
using Microsoft.Extensions.Options;
using System.Data;

namespace Himp.TaskScheduling.Hangfire.Jobs
{
    public class CollectTestDataJob
    {
        #region SQL

        //public const string GET_RILS1810ViewA_SQL = @"
        //    SELECT * 
        //    FROM [dbo].[View_A]
        //    TS_SampleTestTasks
        //    ";

        public const string GET_TESTCODE = @"
            SELECT TESTCODE FROM
            [TaskScheduling].[TS_StandardTestTasks]
            WHERE
            BOTTOMCODE=@BOTTOMCODE
            ";

        public const string EXIST_TESTCODE = @"
            SELECT count(*) FROM
            [TaskScheduling].[TS_StandardTestTasks]
            WHERE
            TESTCODE=@TESTCODE
            ";

        #endregion

        #region 字段

        private readonly ConnectionStringOption _connectionStrings;
        private ILogger _logger;

        #endregion

        /// <summary>
        /// 
        /// </summary>
        /// <param name="logger"></param>
        /// <param name="connectionStrings"></param>
        public CollectTestDataJob(ILogger logger
            , IOptions<ConnectionStringOption> connectionStrings)
        {
            _logger = logger;
            _connectionStrings = connectionStrings.Value;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public bool Exec(CollectTestDataModel data)
        {
            try
            {
                using (IDbConnection db = new SqlConnection(_connectionStrings.RL_Web))
                {
                    db.Open();

                    // 初始化参数
                    int recordIdentity_化验记录表 = -1;
                    int recordIdentity_化验项目明细表 = -1;
                    int unitID = 0;

                    // 获取 testZBIDs 和 testValues 的长度
                    int testZBIDsLen = GetStrArrayLength(data.testZBIDs, ',');
                    int testValuesLen = GetStrArrayLength(data.testValues, ',');

                    #region 检查化验记录

                    // 检查化验记录是否存在
                    recordIdentity_化验记录表 = db.QuerySingleOrDefault<int>(SELECT_RECORD_SQL, new { ThirdCode = data.thirdCode });

                    if (recordIdentity_化验记录表 <= 0)
                    {
                        recordIdentity_化验记录表 = db.QuerySingle<int>(INSERT_RECORD_SQL, new { ThirdCode = data.thirdCode, TestTime = data.testTime, PeopleName = data.peopleName });
                    }

                    #endregion

                    // 检查化验项目明细是否存在
                    string selectItemDetailSql = "SELECT ID FROM HY_ItemDetail WHERE Original_Num = @OriginalRecordNo";
                    recordIdentity_化验项目明细表 = db.QuerySingleOrDefault<int>(selectItemDetailSql
                        , new { OriginalRecordNo = data.originalRecordNo });

                    if (recordIdentity_化验项目明细表 <= 0)
                    {
                        // 获取同样编号的记录数量
                        string countItemDetailSql = "SELECT COUNT(*) FROM HY_ItemDetail WHERE Record_Id = @Record_Id AND Information_Id = @TestProjectID";
                        int count_temp = db.QuerySingle<int>(countItemDetailSql, new { Record_Id = recordIdentity_化验记录表, TestProjectID = data.testProjectID });

                        // 插入新的化验项目明细
                        string insertItemDetailSql = "INSERT INTO HY_ItemDetail (Record_Id, Laboratory_Id, Information_Id, Number, Cancellation, DetectionUser, DetectionTime, Original_Num) " +
                                                     "VALUES (@RecordIdentity_化验记录表, @LabID, @TestProjectID, @Number, NULL, @PeopleName, GETDATE(), @OriginalRecordNo);" +
                                                     "SELECT CAST(SCOPE_IDENTITY() as int);";
                        recordIdentity_化验项目明细表 = db.QuerySingle<int>(insertItemDetailSql, new
                        {
                            RecordIdentity_化验记录表 = recordIdentity_化验记录表,
                            LabID = data.labID,
                            TestProjectID = data.testProjectID,
                            Number = count_temp + 1,
                            PeopleName = data.peopleName,
                            OriginalRecordNo = data.originalRecordNo
                        });
                    }

                    // 插入化验指标及设备信息
                    for (int i = 1; i <= testZBIDsLen; i++)
                    {
                        string testZBID = GetStrArrayStrOfIndex(data.testZBIDs, ',', i);
                        string testValue = GetStrArrayStrOfIndex(data.testValues, ',', i);
                        string testDevice = GetStrArrayStrOfIndex(data.testDeviceID, ',', i);

                        string existNormRecSql = "SELECT COUNT(*) FROM HY_Norm WHERE Norm_Id = @Norm_Id AND ItemDetail_Id = @ItemDetail_Id";
                        int existNormRecCount = db.QuerySingle<int>(existNormRecSql, new
                        {
                            Norm_Id = testZBID,
                            ItemDetail_Id = recordIdentity_化验项目明细表
                        });

                        if (existNormRecCount == 0)
                        {
                            string insertNormSql = "INSERT INTO HY_Norm (Norm_Id, ItemDetail_Id, ZBValues, HY_User, CheckTime, Explain) " +
                                               "VALUES (@TestZBID, @RecordIdentity_化验项目明细表, @TestValue, @GH, @TestTime, NULL);" +
                                               "SELECT CAST(SCOPE_IDENTITY() as int);";
                            int recordIdentity_化验指标表 = db.QuerySingle<int>(insertNormSql, new
                            {
                                TestZBID = testZBID,
                                RecordIdentity_化验项目明细表 = recordIdentity_化验项目明细表,
                                TestValue = decimal.Parse(testValue),
                                GH = data.gh,
                                TestTime = data.testTime
                            });

                            string insertInstrumentSql = "INSERT INTO HY_LaboratoryInstrument (Norm_Id, Instrument_Id) VALUES (@RecordIdentity_化验指标表, @TestDevice)";
                            db.Execute(insertInstrumentSql, new
                            {
                                RecordIdentity_化验指标表 = recordIdentity_化验指标表,
                                TestDevice = testDevice
                            });
                        }
                        else
                        {
                            string updateNormSql = "UPDATE HY_Norm SET ZBValues = @TestValue, HY_User = @GH, CheckTime = @TestTime " +
                                                   "WHERE Norm_Id = @TestZBID AND ItemDetail_Id = @RecordIdentity_化验项目明细表";
                            db.Execute(updateNormSql, new
                            {
                                TestValue = testValue,
                                GH = data.gh,
                                TestTime = data.testTime,
                                TestZBID = testZBID,
                                RecordIdentity_化验项目明细表 = recordIdentity_化验项目明细表
                            });
                        }
                    }

                    //else
                    //{
                    //    // 更新化验指标信息
                    //    for (int i = 1; i <= testZBIDsLen; i++)
                    //    {
                    //        string testZBID = GetStrArrayStrOfIndex(data.testZBIDs, ',', i);
                    //        string testValue = GetStrArrayStrOfIndex(data.testValues, ',', i);

                    //        string updateNormSql = "UPDATE HY_Norm SET ZBValues = @TestValue, HY_User = @GH, CheckTime = @TestTime " +
                    //                               "WHERE Norm_Id = @TestZBID AND ItemDetail_Id = @RecordIdentity_化验项目明细表";
                    //        db.Execute(updateNormSql, new
                    //        {
                    //            TestValue = testValue,
                    //            GH = data.gh,
                    //            TestTime = data.testTime,
                    //            TestZBID = testZBID,
                    //            RecordIdentity_化验项目明细表 = recordIdentity_化验项目明细表
                    //        });
                    //    }
                    //}
                }
            }
            catch (Exception e)
            {

            }
            
            //using (IDbConnection dbConnection = new SqlConnection(_connectionStrings.RL_Web))
            //{
            //    dbConnection.Open();
            //    // 存储过程名称
            //    string storedProcedure = "sp_CollectTestData";
            //    // 执行存储过程
            //    int r = dbConnection.Execute(storedProcedure, data, commandType: CommandType.StoredProcedure);
            //    _logger.LogInformation("执行存储过程sp_CollectTestData:" + r);
            //}

            return true;
        }

        #region SQL

        //检查化验记录是否存在
        public const string SELECT_RECORD_SQL = "SELECT ID FROM HY_Record WHERE HY_Code = @ThirdCode";

        //插入新的化验记录
        public const string INSERT_RECORD_SQL = "INSERT INTO HY_Record (HY_Code, Type, Accept_Time, Accept_User,IsAuto) VALUES (UPPER(@ThirdCode), N'全水、分析', @TestTime, @PeopleName ,1);" +
                                 "SELECT CAST(SCOPE_IDENTITY() as int);";

        #endregion

        static int GetStrArrayLength(string str, char delimiter)
        {
            return str.Split(delimiter).Length;
        }

        static string GetStrArrayStrOfIndex(string str, char delimiter, int index)
        {
            return str.Split(delimiter)[index - 1];
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public string BottomCodeConvertTestCode(string bottomCode)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(_connectionStrings.RL_Web))
                {
                    connection.Open();
                    var testCode = connection.Query<string>(GET_TESTCODE,new { BOTTOMCODE = bottomCode });

                    return testCode.FirstOrDefault();
                }
            }
            catch (Exception e)
            {
                return string.Empty;
            }
        }

        public bool ExistTestCode(string testCode)
        {
            try
            {
                using (IDbConnection connection = new SqlConnection(_connectionStrings.RL_Web))
                {
                    connection.Open();
                    var exist = connection.ExecuteScalar<int>(EXIST_TESTCODE, new { TESTCODE = testCode });

                    return exist>0;
                }
            }
            catch (Exception e)
            {
                return false ;
            }
        }

    }

    public class CollectTestDataModel
    {
        /// <summary>
        /// 化验编码
        /// </summary>
        public string thirdCode { get; set; }

        /// <summary>
        /// 工号
        /// </summary>
        public string gh { get; set; }

        /// <summary>
        /// 名称
        /// </summary>
        public string peopleName { get; set; }

        /// <summary>
        /// 化验时间
        /// </summary>
        public string testTime { get; set; }

        /// <summary>
        /// 原记录编码
        /// </summary>
        public string originalRecordNo { get; set; }

        /// <summary>
        /// 实验室编号
        /// </summary>
        public string labID { get; set; }

        /// <summary>
        /// 检测项目编号
        /// </summary>
        public string testProjectID { get; set; }

        /// <summary>
        /// 检测仪器编号
        /// </summary>
        public string testDeviceID { get; set; }

        /// <summary>
        /// 检测单位
        /// </summary>
        public string unitID { get; set; }

        /// <summary>
        /// 检测指标集合
        /// </summary>
        public string testZBIDs { get; set; }

        /// <summary>
        /// 检测值集合
        /// </summary>
        public string testValues { get; set; }
    }
}
